package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class UserDao
{
  static Logger logger = LoggerFactory.getLogger(UserDao.class);
  public static final String TABLE = "xt_user";
  public static final String TABLE1 = "xt_server";
  
  public static List load(String userId, String userName)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    List list = null;
    sql = "SELECT * FROM xt_user WHERE 1=1 ";
    if ((userId != null) && (userId.length() > 0)) {
      sql = 
        sql + "AND user_id like ?";
    }
    if ((userName != null) && (userName.length() > 0)) {
      sql = 
        sql + "AND user_name like ?";
    }
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userId != null) && (userId.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userId + "%");
      }
      if ((userName != null) && (userName.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userName + "%");
      }
      rs = pstmt.executeQuery();
      list = new ArrayList();
      while (rs.next()) {
        list.add(rsToBean(rs));
      }
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freeResultSet(rs);
      DbClose.freeStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    return list;
  }
  
  public static List browse(String userId, String userName)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    List list = null;
    sql = "SELECT * FROM xt_user WHERE user_id not in (select userid from da_q_ek_user ) ";
    if ((userId != null) && (userId.length() > 0)) {
      sql = 
        sql + "AND user_id like ?  ";
    }
    if ((userName != null) && (userName.length() > 0)) {
      sql = 
        sql + " AND user_name like ? ";
    }
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userId != null) && (userId.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userId + "%");
      }
      if ((userName != null) && (userName.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userName + "%");
      }
      rs = pstmt.executeQuery();
      list = new ArrayList();
      while (rs.next()) {
        list.add(rsToBean(rs));
      }
    }
    catch (SQLException e)
    {
      logger.error("db error", e);
    }
    finally
    {
      DbClose.freeResultSet(rs);
      DbClose.freeStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    return list;
  }
  
  public static int count(String userId, String userName)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT count(*) FROM xt_user WHERE 1=1 ";
    if ((userId != null) && (userId.length() > 0)) {
      sql = 
        sql + " AND user_id LIKE ? ";
    }
    if ((userName != null) && (userName.length() > 0)) {
      sql = 
        sql + " AND user_name LIKE ? ";
    }
    logger.debug(sql);
    int j = 0;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userId != null) && (userId.length() > 0)) {
        pstmt.setString(i++, userId);
      }
      if ((userName != null) && (userName.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userName + "%");
      }
      rs = pstmt.executeQuery();
      if (rs.next()) {
        j = rs.getInt(1);
      }
    }
    catch (SQLException e)
    {
      logger.error("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    return j;
  }
  
  public static User load(String userName)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT * FROM xt_user WHERE user_name = ?  ";
    logger.debug(sql);
    User user = null;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      int i = 1;
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(i++, userName);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        user = rsToBean(rs);
      }
    }
    catch (SQLException e)
    {
      logger.error("db error", e);
    }
    finally
    {
      DbClose.freeResultSet(rs);
      DbClose.freeStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    return user;
  }
  
  public static User login(String userName,String password)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT * FROM xt_user WHERE user_name = ? and ef_password = ? ";
    logger.debug(sql);
    User user = null;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      int i = 1;
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(i++, userName);
      pstmt.setString(i++, password);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        user = rsToBean(rs);
      }
    }
    catch (SQLException e)
    {
      logger.error("db error", e);
    }
    finally
    {
      DbClose.freeResultSet(rs);
      DbClose.freeStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    return user;
  }
  
  public static User loadById(String userId)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT * FROM xt_user WHERE user_id = ?  ";
    logger.debug(sql);
    User user = null;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      int i = 1;
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(i++, userId);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        user = rsToBean(rs);
      }
    }
    catch (SQLException e)
    {
      logger.error("db error", e);
    }
    finally
    {
      DbClose.freeResultSet(rs);
      DbClose.freeStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    return user;
  }
  
  static User rsToBean(ResultSet rs)
    throws SQLException
  {
    User bean = new User();
    bean.setUserId(rs.getString("user_id"));
    bean.setUserName(rs.getString("user_name"));
    bean.setPassword(rs.getString("password"));
    bean.setDjjg(rs.getString("djjg"));
    bean.setGxdwdm(rs.getString("gxdwdm"));
    bean.setSpqx(rs.getString("spqx"));
    bean.setFullName(rs.getString("full_name"));
    bean.setEfPassword(rs.getString("ef_password"));
    return bean;
  }

  public static int updatePassword(User user) {
    logger.debug("start to update ef_password for user:{},{}",user.getUserName(),user.getUserId());
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "update xt_user set ef_password = ? WHERE user_id = ?  ";
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      int i = 1;
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(i++, user.getEfPassword());
      pstmt.setString(i++,user.getUserId());
      return pstmt.executeUpdate();

    }
    catch (SQLException e)
    {
      logger.error("updatePassword error", e);
      return -1;
    }
    finally
    {
      DbClose.freeResultSet(rs);
      DbClose.freeStatement(pstmt);
      DbClose.freeConnection(conn);
    }
  }

  public static int updatePassword(String userName,String password) {
    logger.debug("start to update ef_password for user:{},{}",userName,password);
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "update xt_user set ef_password = ? WHERE user_name = ?  ";
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      int i = 1;
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(i++, password);
      pstmt.setString(i++,userName);
      return pstmt.executeUpdate();

    }
    catch (SQLException e)
    {
      logger.error("updatePassword error", e);
      return -1;
    }
    finally
    {
      DbClose.freeResultSet(rs);
      DbClose.freeStatement(pstmt);
      DbClose.freeConnection(conn);
    }
  }

  public static List search(String userName) {
    int page = 1,pageSize=10;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT * FROM (SELECT rownum r,s.* from (select user_name,full_name FROM xt_user a where 1=1 ";
    if ((userName != null) && (userName.length() > 0)) {
      sql = sql + " AND a.user_name LIKE ? ";
    }
    sql = sql + ") s where rownum <=? * ?  )t WHERE t.r > (? - 1) * ? ";

    try
    {
      logger.debug("sql:{}",sql);
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userName != null) && (userName.length() > 0)) {
        pstmt.setString(i++,
                "%" + userName + "%");
      }

      pstmt.setInt(i++, page);
      pstmt.setInt(i++, pageSize);
      pstmt.setInt(i++, page);
      pstmt.setInt(i++, pageSize);

      rs = pstmt.executeQuery();
      List list = new ArrayList();
      while (rs.next()) {
        Map map = new HashMap();
        map.put("data",rs.getString("user_name"));
        map.put("value",rs.getString("full_name"));
        list.add(map);
      }
      logger.debug(
              list.size() + "size");
      List localList1 = list;
      return localList1;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }

    return null;
  }
}
